Performance Recommendations

Oracle DB Tuning Recommendations

  • As part of the performance analysis done while testing Cycle, we have come across with an ADDM recommendation to increase in the size of the log files as 2,048 MB in order to hold at least 20 minutes of redo information. This can reduce the wait time in between the log file switches results in performance improvement.

  • "aio-max-nr" : set this parameter value to minimum value suggested by oracle (1048576). Maximum of aio-max-nr parameter can be calculated by – aio-max-nr =no of process per DB * no of databases * 4096. This helps HyperScale to perform optimally, in an environment that involves heavy I/O workloads.

  • Run gather statistics for all objects within the specified schema (schema_name) using an automatic sample size determination.

    • exec dbms_stats.gather_schema_stats('Schema_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

Index Recommendation Disclaimer

These indexes were identified during performance testing and may be beneficial depending on configuration and query patterns. Customers should validate them in a lower environment before applying in production

Oracle DB Index Recommendations

  1. CREATE INDEX IX_2_ASCLIENT ON ASCLIENT (TAXID, CLIENTGUID);

  2. CREATE INDEX IX_4_ASCLIENTRELATIONSHIP ON ASCLIENTRELATIONSHIP (PRIMARYRELATIONSHIPTYPE, PRIMARYCLIENTGUID, SECONDARYCLIENTGUID);

  3. CREATE INDEX IX_5_ASVALUATION ON ASVALUATION (SEEDDEPOSITGUID, ACTIVITYGUID, MONEYTYPECODE);

  4. CREATE INDEX IX_2_ASDEPOSITVALUATIONEFFECT ON ASDEPOSITVALUATIONEFFECT (SEEDDEPOSITGUID, ACTIVITYGUID, MONEYTYPECODE);

  5. CREATE INDEX IX_1_ASALLOCATIONSET ON ASALLOCATIONSET (GROUPGUID);

  6. CREATE INDEX IX_1_ASBILLDETAIL ON ASBILLDETAIL (BILLDETAILREFERENCEGUID);

  7. CREATE INDEX IX_1_ASBILLDETAILGROUP ON ASBILLDETAILGROUP (BILLDETAILREFERENCEGUID);

  8. CREATE INDEX IX_14_ASACTIVITY ON ASACTIVITY (POLICYGUID, ACTIVEFROMDATE);

  9. CREATE INDEX IX_1_ASWORKFLOWTASK ON ASWORKFLOWTASK (ENTITYGUID);

  10. CREATE INDEX IX_1_ASBILLDETAILRECONCILIATION ON ASBILLDETAILRECONCILIATION (BILLDETAILGUID, STATUSCODE);

  11. CREATE INDEX IX_2_ASBILLDETAIL ON ASBILLDETAIL (STATUS, DUEDATE);

  12. CREATE INDEX IX_1_ASCLASSMEMBERSHIP ON ASCLASSMEMBERSHIP (CLIENTGUID, EFFECTIVEFROM, CLASSGUID);

  13. CREATE INDEX IX_4_ASCYCLE ON ASCYCLE (CYCLEGUID, CLIENTGUID);

  14. CREATE INDEX IX_3_ASCYCLE ON ASCYCLE (CYCLEGUID, POLICYGUID);

  15. CREATE INDEX IX_2_ASCYCLEDETAIL ON ASCYCLEDETAIL (CYCLEGUID, WORKITEMGUID);

  16. CREATE INDEX IX_3_ASCLIENT ON ASCLIENT (LASTNAME, ENTITYTYPECODE, TYPECODE, CLIENTGUID);

  17. CREATE INDEX IX_4_ASCLIENT ON ASCLIENT (ENTITYTYPECODE, DATEOFBIRTH, TYPECODE, CLIENTGUID);

  18. CREATE INDEX IX_8_ASROLE ON ASROLE (COMPANYGUID, CLIENTGUID);

  19. CREATE INDEX IX_3_ASPOLICY ON ASPOLICY (COMPANYGUID, PLANDATE, PLANGUID, POLICYGUID);

  20. CREATE INDEX IX_3_ASSUSPENSE ON ASSUSPENSE (COMPANYGUID, BANKNAME, SUSPENSEGUID);

  21. CREATE INDEX IX_4_ASSUSPENSE ON ASSUSPENSE (COMPANYGUID, TYPECODE, SUSPENSEGUID);

  22. CREATE INDEX IX_13_ASRATE ON ASRATE (RATEGROUPGUID, RATEGUID);

  23. CREATE INDEX IX_1_ASTIMESLICEACTIVITY ON ASTIMESLICEACTIVITY (OBJECTGUID, OBJECTTYPE, ACTIVITYGUID);

  24. CREATE INDEX IX_15_ASACTIVITY ON ASACTIVITY (CLIENTNUMBER);

  25. CREATE INDEX IX_1_ASTAG ON ASTAG (ENTITYTYPE, CLIENTNUMBER, EFFECTIVEDATE);

  26. CREATE INDEX IX_5_ASSUSPENSE ON ASSUSPENSE (TO_CHAR(SUSPENSENUMBER));

Optional Drop Candidates:

-- Drop overlapping shorter index:

-- IX_4_ASVALUATION on ASVALUATION (SEEDDEPOSITGUID, ACTIVITYGUID)

-- Covered by IX_5_ASVALUATION on ASVALUATION (SEEDDEPOSITGUID, ACTIVITYGUID, MONEYTYPECODE)

DROP INDEX IX_4_ASVALUATION;

-- Drop overlapping shorter index:

-- IX_1_ASDEPOSITVALUATIONEFFECT on ASDEPOSITVALUATIONEFFECT (SEEDDEPOSITGUID, ACTIVITYGUID)

-- Covered by IX_2_ASDEPOSITVALUATIONEFFECT on ASDEPOSITVALUATIONEFFECT (SEEDDEPOSITGUID, ACTIVITYGUID, MONEYTYPECODE)

DROP INDEX IX_1_ASDEPOSITVALUATIONEFFECT;

JVM Recommendations

Non Production Environment

  1. Considering optimal usage of JVM and coherence resources, customer should have at-least 2 (Cycle/OIPA/ServiceLayer) JVMs per cluster.

    Ideal would be to have 4 (Cycle/OIPA/ServiceLayer) JVMs per coherence cluster or more depending on the workload and processing needs.

  2. When 2 (Cycle/OIPA/ServiceLayer) JVMs run per machine, the cpu and memory resources are optimally managed, there is less network communication between nodes deployed on same machine.

Production Environment

  1. Considering optimal usage of JVM and coherence resources, customer should have at-least 4 (Cycle/OIPA/ServiceLayer) JVMs per cluster.

    Ideal would be to have 6(Cycle/OIPA/ServiceLayer) JVMs per coherence cluster or more depending on the workload and processing needs.

  2. When 4 (Cycle/OIPA/ServiceLayer) JVMs run per machine, the cpu and memory resources are optimally managed, there is less network communication between nodes deployed on same machine.

Network Recommendations

It is recommended to run the application and DB servers in same network region. So that network latency will be minimal.

Heap Size Recommendations

  1. Recommended heap is 6gb size. A heap size beyond 8gb can have higher memory fragmentation and in order to deal with this memory fragmentation for large heap, garbage collectors have to work extra for compactness of the heap space and efficient management of the old and young generation spaces of heap. Lower memory footprints are usually easier to manage from a GC standpoint.

  2. Ensure JVMs initial and maximum head size is always set to equal. This will allow heap to be set to max at the time of startup for the app server and will not grow heap when needed. Ensure at-least 30% of heap is always free.

OS Recommendations

Recommended operating system to deploy Cycle clusters is Linux/Unix. Linux kernel has better resource utilization, management and optimization as compared to Windows Servers. Especially when it comes to Thread Synchronization.

GC Recommendations

Recommended to use G1GC from Java 11 and higher versions. The aim of G1GC is to strike a balance between latency and throughput. The G1 garbage collector attempts to achieve high throughput by meeting pause time goals with high probability.

DB Connection Pool Size Recommendations

OIPA and Cycle leverages 4 data sources for different purpose. The DB connection pools size can be adjusted based on the usage and number of open cursors/session for the test run. The recommended DB connections per data source is 100 connections per JVM. It can be set lowered if the connections are unused.

Troubleshooting Tips

In case of a high CPU utilization on the database, an immediate action would be to take a AWR and ADDM report for Oracle database and SQL trace file for SQL server database and look for top five or top 10 SQL queries that are under performing.